﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Web;

/// <summary>
/// Provides logic for the application
/// </summary>
public class LoanBLL
{
    LoanDAL dataObject; //Data access abject
    loanDataSetTableAdapters.GetLoansDetailedInterestProjectionAddonTableAdapter addonLoansDetailedInterestProjectionTableAdapter;
    loanDataSetTableAdapters.GetLoansDetailedInterestProjectionRedBalTableAdapter redbalLoansDetailedInterestProjectionTableAdapter;

    /*wrapper used to return the result of the insert operation*/
    UploadResult uploadResult;

    public LoanBLL()
    {
        try
        {
            /*init data access layer object*/
            dataObject = new LoanDAL();
            uploadResult = new UploadResult();
            addonLoansDetailedInterestProjectionTableAdapter =
                new loanDataSetTableAdapters.GetLoansDetailedInterestProjectionAddonTableAdapter();
            redbalLoansDetailedInterestProjectionTableAdapter =
                new loanDataSetTableAdapters.GetLoansDetailedInterestProjectionRedBalTableAdapter();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    /// <summary>
    /// Accepts a data table with loan records and pass it to the data access layer DAL
    /// </summary>
    /// <param name="table">data table consisting of loan reocrds</param>
    /// <param name="listError">store errors generated within the insert operation in the DAL</param>
    /// <returns>returns the number of loans inserted</returns>
    public UploadResult InsertAddonLoans(DataTable loanRecordsTable)
    {

        Error recordError; //used to store an error if the current record is faulty/invalid   
        Loan loanRecord; // used to store the loan record details
        uploadResult = new UploadResult();//Wrapper that is used to returned the result of the upload operation

        string loanNo = string.Empty;
        string startDate = string.Empty;
        string term = string.Empty;
        string principal = string.Empty;
        string period = string.Empty;
        string product = string.Empty;
        string interestRate = string.Empty;

        if (loanRecordsTable != null) //check to ensure that table passed in is initialized
        {
            if (dataObject.OpenDbConnection())
            {
                /*loop through loan records and insert into the db or log error if record is invalid */
                foreach (DataRow row in loanRecordsTable.Rows)
                {
                    /*stores the error for the current record*/
                    recordError = new Error();

                    /*get data from row (record)*/
                    loanNo = row[0].ToString();
                   startDate = row[1].ToString();    
                    principal = row[2].ToString();
                    term = row[3].ToString();
                                     
                    period = (row[4].ToString().Trim()).ToLower();
                    product = row[5].ToString();
                    interestRate = row[6].ToString();


                    /*Check if record is blank - often happens at the end of the excel sheet*/
                    if (loanNo == "" && startDate == "" && principal == "" && term == "" && period == "" && product == "")
                    {
                        uploadResult.SummaryRecordsResult.BlankRecords++;
                    }/*check if one or more field is blank*/
                    else if (loanNo == "" || startDate == "" || principal == "" || term == "" || period == "" || product == "")
                    {
                        /*increment totalresords by 1*/
                        uploadResult.SummaryRecordsResult.TotalRecords++;

                        loanRecord = new Loan();

                        /*check if the loan number is valid*/
                        if (loanNo == "")
                        {
                            recordError.Message = "Invalid Loan Number";
                        }
                        else
                        {
                            loanRecord.LoanNo = loanNo;
                        }

                        /*check if the disbursment date is valid*/
                        if (startDate == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Disburement Date" : recordError.Message + " | Invalid Disburement Date";
                        }
                        else
                        {
                            try
                            {
                                loanRecord.StartDate = DateTime.Parse(startDate);
                            }
                            catch
                            {
                                recordError.Message = (recordError.Message == "") ? "Invalid Disburement Date" : recordError.Message + " | Invalid Disburement Date";
                            }
                        }

                        /*check if the principal is valid*/
                        if (principal == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Loan Amount" : recordError.Message + " | Invalid Loan Amount";
                        }
                        else
                        {
                            try
                            {
                                loanRecord.Principal = Decimal.Parse(principal);
                            }
                            catch
                            {
                                recordError.Message = (recordError.Message == "") ? "Invalid Loan Amount" : recordError.Message + " | Invalid Loan Amount";
                            }
                        }

                        /*check if the term is valid*/
                        if (term == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                        }
                        else
                        {
                            try
                            {
                                loanRecord.Term = Int32.Parse(term);
                            }
                            catch
                            {
                                recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                            }
                        }

                        /*check if the period is valid*/
                        if (period == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Fequency" : recordError.Message + " | Invalid Fequency";
                        }
                        else
                        {
                            loanRecord.Period = period;
                        }

                        /*check if the product is valid*/
                        if (product == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Product" : recordError.Message + " | Invalid Product";
                        }
                        else
                        {
                            loanRecord.Product = product;
                        }

                        uploadResult.SummaryRecordsResult.InvalidRecords++;
                        /*setup loan record error details and logg error*/
                        recordError.Date = DateTime.Now;
                        recordError.Type = "LoanRecordError";
                        recordError.RecordNo = uploadResult.SummaryRecordsResult.TotalRecords + 1; //data starts at row 2, row 1 is the header
                        recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);
                        uploadResult.Log.Add(recordError);
                    }
                    else/*no blank fields found- perform validation*/
                    {
                        /*increment totalresords by 1*/
                        uploadResult.SummaryRecordsResult.TotalRecords++;

                        loanRecord = new Loan();

                        /*assign loan no to loanrecord object*/
                        loanRecord.LoanNo = loanNo;

                        /*assign product to loanrecord object*/
                        loanRecord.Product = product;

                        /*check if the disbursement date is valid*/
                        try
                        {
                            loanRecord.StartDate = DateTime.Parse(startDate);
                            if (loanRecord.StartDate > DateTime.Now)
                                recordError.Message = (recordError.Message == "") ? "Invalid Disburement Date" : recordError.Message + " | Invalid Disburement Date";
                        }
                        catch
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Disburement Date" : recordError.Message + " | Invalid Disburement Date";
                        }
                        /*check if the loan amount is valid*/
                        try
                        {
                            loanRecord.Principal = Decimal.Parse(principal);
                            if (loanRecord.Principal <= 0)
                                recordError.Message = (recordError.Message == "") ? "Invalid Loan Amount" : recordError.Message + " | Invalid Loan Amount";
                        }
                        catch
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Loan Amount" : recordError.Message + " | Invalid Loan Amount";
                        }

                        /*checked if the term is valid*/
                        try
                        {
                            loanRecord.Term = Int32.Parse(term);
                            if (loanRecord.Term <= 0)
                                recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                        }
                        catch
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                        }

                        try
                        {
                            loanRecord.InterestRate = Decimal.Parse(interestRate);
                            if (loanRecord.InterestRate < 0)
                                recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                        }
                        catch
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                        }

                        /*check if the period is valid*/
                        if (period == "weekly" || period == "monthly" || period == "bi-monthly")
                            loanRecord.Period = period;
                        else if (period == "m")
                            loanRecord.Period = "monthly";
                        else if (period == "w")
                            loanRecord.Period = "weekly";
                        else if (period == "bi monthly" || period == "bimonthly" || period == "f")
                            loanRecord.Period = "bi-monthly";
                        else
                            recordError.Message = (recordError.Message == "") ? "Invalid Fequency" : recordError.Message + " | Invalid Fequency";

                        /*log eror and skip processing if the recordError object is populated*/
                        if (recordError.Message != "")
                        {
                            /*log the record as invalid in the upload summary report*/
                            uploadResult.SummaryRecordsResult.InvalidRecords++;
                            /*setup loan record error details and logg error*/
                            recordError.Date = DateTime.Now;
                            recordError.Type = "LoanRecordError";
                            recordError.RecordNo = uploadResult.SummaryRecordsResult.TotalRecords + 1; //data starts at row 2, row 1 is the header
                            /*convert error object to xml to be stored in the db*/
                            recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);
                            uploadResult.Log.Add(recordError);

                        }
                        else /*record is valid and ready for pocessing*/
                        {/*flag record as valid*/
                            uploadResult.SummaryRecordsResult.ValidRecords++;

                            int recordInserted = 0;//store the value return by the command object

                            try
                            {
                                recordInserted = dataObject.InsertAddonLoan(loanRecord, out recordError);
                            }
                            catch
                            {/*re throw exception and cancel insert operation*/
                                throw new Exception("Unable to insert record in database");
                            }

                            /*check if the db encountered an error during the insert operation*/
                            if (recordInserted > 0)
                                uploadResult.SummaryRecordsResult.InsertedRecords++;
                            else
                            {
                                /*setup loan record error details and logg error*/
                                recordError.Date = DateTime.Now;
                                recordError.Type = "LoanRecordError";
                                /*convert error object to xml to be stored in the db*/
                                recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);
                                recordError.RecordNo = uploadResult.SummaryRecordsResult.TotalRecords + 1; //data starts at row 2, row 1 is the header
                                uploadResult.Log.Add(recordError);
                                /*check for duplicate error and update summary results*/
                                if (recordError.Code == 2627)
                                    uploadResult.SummaryRecordsResult.DuplicateRecords++;
                            }
                        }
                    }
                }//end of foreach 
                dataObject.CloseDbConnection();
            }//end if (opendbconnection)       
            else
                throw new Exception("Unable to open database connection");
        }//end if loanrecord is not null
        return uploadResult;
    }

    public UploadResult InsertReducingBalanceLoans(DataTable loanRecordsTable)
    {
        Error recordError; //used to store an error if the current record is faulty/invalid   
        ReducingBalanceLoan loanRecord; // used to store the loan record details
        uploadResult = new UploadResult();//Wrapper that is used to returned the result of the upload operation

        string loanNo = string.Empty;
        string startDate = string.Empty;
        string term = string.Empty;
        string principal = string.Empty;
        string period = string.Empty;
        string product = string.Empty;
        string interestRate = string.Empty;

        if (loanRecordsTable != null) //check to ensure that table passed in is initialized
        {
            if (dataObject.OpenDbConnection())
            {
                /*loop through loan records and insert into the db or log error if record is invalid */
                foreach (DataRow row in loanRecordsTable.Rows)
                {
                    /*stores the error for the current record*/
                    recordError = new Error();

                    /*get data from row (record)*/
                    loanNo = row[0].ToString();
                    startDate = row[1].ToString();
                    principal = row[2].ToString();
                    term = row[3].ToString();
                    period = row[4].ToString().Trim().ToLower();
                    product = row[5].ToString().Trim();
                    interestRate = row[6].ToString();

                    /*Check if record is blank - often happens at the end of the excel sheet*/
                    if (loanNo == "" && startDate == "" && principal == "" && term == "" && period == "" && product == "" && interestRate == "")
                    {
                        uploadResult.SummaryRecordsResult.BlankRecords++;
                    }/*check if one or more field is blank*/
                    else if (loanNo == "" || startDate == "" || principal == "" || term == "" || period == "" || product == "" || interestRate == "")
                    {
                        /*increment totalresords by 1*/
                        uploadResult.SummaryRecordsResult.TotalRecords++;

                        loanRecord = new ReducingBalanceLoan();

                        /*check if the loan number is valid*/
                        if (loanNo == "")
                        {
                            recordError.Message = "Invalid Loan Number";
                        }
                        else
                        {
                            loanRecord.LoanNo = loanNo;
                        }

                        /*check if the disbursment date is valid*/
                        if (startDate == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Disburement Date" : recordError.Message + " | Invalid Disburement Date";
                        }
                        else
                        {
                            try
                            {
                                loanRecord.StartDate = DateTime.Parse(startDate);
                            }
                            catch
                            {
                                recordError.Message = (recordError.Message == "") ? "Invalid Disburement Date" : recordError.Message + " | Invalid Disburement Date";
                            }
                        }

                        /*check if the principal is valid*/
                        if (principal == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Loan Amount" : recordError.Message + " | Invalid Loan Amount";
                        }
                        else
                        {
                            try
                            {
                                loanRecord.Principal = Decimal.Parse(principal);
                            }
                            catch
                            {
                                recordError.Message = (recordError.Message == "") ? "Invalid Loan Amount" : recordError.Message + " | Invalid Loan Amount";
                            }
                        }

                        /*check if the term is valid*/
                        if (term == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                        }
                        else
                        {
                            try
                            {
                                loanRecord.Term = Int32.Parse(term);
                            }
                            catch
                            {
                                recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                            }
                        }

                        /*check if the period is valid*/
                        if (period == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Fequency" : recordError.Message + " | Invalid Fequency";
                        }
                        else
                        {
                            loanRecord.Period = period;
                        }


                        /*check if product is blank*/
                        if (product == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Product" : recordError.Message + " | Invalid Product";
                        }
                        else
                        {
                            loanRecord.Product = product;
                        }

                        /*check if the interest rate is blank*/
                        if (interestRate == "")
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Interest Rate" : recordError.Message + " | Invalid Interest Rate";
                        }
                        else
                        {
                            try
                            {
                                loanRecord.InterestRate = Decimal.Parse(interestRate);
                            }
                            catch
                            {
                                recordError.Message = (recordError.Message == "") ? "Invalid Interest Rate" : recordError.Message + " | Invalid Interest Rate";
                            }
                        }

                        uploadResult.SummaryRecordsResult.InvalidRecords++;
                        /*setup loan record error details and logg error*/
                        recordError.Date = DateTime.Now;
                        recordError.Type = "LoanRecordError";
                        recordError.RecordNo = uploadResult.SummaryRecordsResult.TotalRecords + 1; //data starts at row 2, row 1 is the header
                        recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);
                        uploadResult.Log.Add(recordError);
                    }
                    else/*no blank fields found- perform validation*/
                    {
                        loanRecord = new ReducingBalanceLoan();
                        recordError = new Error();

                        /*increment totalresords by 1*/
                        uploadResult.SummaryRecordsResult.TotalRecords++;

                        /*assign loan no to loanrecord object - no validation required since it is not blank*/
                        loanRecord.LoanNo = loanNo;
                        /*assign loan no to loanrecord object - no validation required since it is not blank*/
                        loanRecord.Product = product;

                        /*check if the disbursement date is valid*/
                        try
                        {
                            loanRecord.StartDate = DateTime.Parse(startDate);
                            if (loanRecord.StartDate > DateTime.Now)
                                recordError.Message = (recordError.Message == "") ? "Invalid Disburement Date" : recordError.Message + " | Invalid Disburement Date";
                        }
                        catch
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Disburement Date" : recordError.Message + " | Invalid Disburement Date";
                        }
                        /*check if the loan amount is valid*/
                        try
                        {
                            loanRecord.Principal = Decimal.Parse(principal);
                            if (loanRecord.Principal <= 0)
                                recordError.Message = (recordError.Message == "") ? "Invalid Loan Amount" : recordError.Message + " | Invalid Loan Amount";
                        }
                        catch
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Loan Amount" : recordError.Message + " | Invalid Loan Amount";
                        }

                        /*checked if the term is valid*/
                        try
                        {
                            loanRecord.Term = Int32.Parse(term);
                            if (loanRecord.Term <= 0)
                                recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                        }
                        catch
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Term" : recordError.Message + " | Invalid Term";
                        }

                        /*check if the period is valid*/
                        if (period == "weekly" || period == "monthly" || period == "bi-monthly")
                            loanRecord.Period = period;
                        else if (period == "m")
                            loanRecord.Period = "monthly";
                        else if (period == "w")
                            loanRecord.Period = "weekly";
                        else if (period == "bi monthly" || period == "bimonthly" || period == "f")
                            loanRecord.Period = "bi-monthly";
                        else
                            recordError.Message = (recordError.Message == "") ? "Invalid Fequency" : recordError.Message + " | Invalid Fequency";

                        /*checked if the interest rate is valid*/
                        try
                        {
                            loanRecord.InterestRate = Decimal.Parse(interestRate);
                            if (loanRecord.InterestRate <= 0 || loanRecord.InterestRate > 100)
                                recordError.Message = (recordError.Message == "") ? "Invalid Interest Rate" : recordError.Message + " | Invalid Interest Rate";
                        }
                        catch
                        {
                            recordError.Message = (recordError.Message == "") ? "Invalid Interest Rate" : recordError.Message + " | Invalid Interest Rate";
                        }

                        /*log eror and skip processing if the recordError object is populated*/
                        if (recordError.Message != "")
                        {
                            /*log the record as invalid in the upload summary report*/
                            uploadResult.SummaryRecordsResult.InvalidRecords++;
                            /*setup loan record error details and logg error*/
                            recordError.Date = DateTime.Now;
                            recordError.Type = "LoanRecordError";
                            recordError.RecordNo = uploadResult.SummaryRecordsResult.TotalRecords + 1; //data starts at row 2, row 1 is the header
                            /*convert error object to xml to be stored in the db*/
                            recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);
                            uploadResult.Log.Add(recordError);

                        }
                        else /*record is valid and ready for pocessing*/
                        {/*flag record as valid*/
                            uploadResult.SummaryRecordsResult.ValidRecords++;

                            int recordInserted = 0;//store the value return by the command object

                            try
                            {
                                recordInserted = dataObject.InsertReducingBalanceLoann(loanRecord, out recordError);
                            }
                            catch
                            {/*re throw exception and cancel insert operation*/
                                throw new Exception("Unable to insert record in database");
                            }

                            /*check if the db encountered an error during the insert operation*/
                            if (recordInserted > 0)
                                uploadResult.SummaryRecordsResult.InsertedRecords++;
                            else
                            {
                                /*setup loan record error details and logg error*/
                                recordError.Date = DateTime.Now;
                                recordError.Type = "LoanRecordError";
                                /*convert error object to xml to be stored in the db*/
                                recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);

                                recordError.RecordNo = uploadResult.SummaryRecordsResult.TotalRecords + 1; //data starts at row 2, row 1 is the header

                                uploadResult.Log.Add(recordError);
                                /*check for duplicate error and update summary results*/
                                if (recordError.Code == 2627)
                                    uploadResult.SummaryRecordsResult.DuplicateRecords++;

                            }
                        }
                    }
                }//end foreach loop
                dataObject.CloseDbConnection();
            }//end if (opendbconnection)       
            else
                throw new Exception("Unable to open database connection");
        }//end if loanrecord is not null
        return uploadResult;
    }

    public DataTable GetAddonLoansDetailedInterestProjection(DateTime fromReportDate, DateTime toReportDate, decimal interestRate, int pageIndex, int pageSize)
    {
        if ((fromReportDate.Year == toReportDate.Year && fromReportDate.Month <= toReportDate.Month) || fromReportDate <= toReportDate)
        {
            return addonLoansDetailedInterestProjectionTableAdapter.GetData(
                fromReportDate,
                toReportDate,
                interestRate,
                pageIndex,
                pageSize);
        }
        else
        {
            return null;
        }
    }

    public DataTable GetRedBalLoansDetailedInterestProjection(DateTime fromReportDate, DateTime toReportDate, int pageIndex, int pageSize)
    {
        if ((fromReportDate.Year == toReportDate.Year && fromReportDate.Month <= toReportDate.Month) || fromReportDate <= toReportDate)
        {
            return redbalLoansDetailedInterestProjectionTableAdapter.GetData(
                fromReportDate,
                toReportDate,
                pageIndex,
                pageSize);
        }
        else
        {
            return null;
        }
    }

    public int GetAddonLoansDetailedInterestProjectionCount(DateTime fromReportDate, DateTime toReportDate)
    {
        if ((fromReportDate.Year == toReportDate.Year && fromReportDate.Month <= toReportDate.Month) || fromReportDate <= toReportDate)
        {
            return (int)addonLoansDetailedInterestProjectionTableAdapter.GetLoansDetailedInterestProjectionCountAddon(fromReportDate, toReportDate);
        }
        else
            return -1;
    }

    public int GetRedBalLoansDetailedInterestProjectionCount(DateTime fromReportDate, DateTime toReportDate)
    {
        if ((fromReportDate.Year == toReportDate.Year && fromReportDate.Month <= toReportDate.Month) || fromReportDate <= toReportDate)
        {
            return (int)redbalLoansDetailedInterestProjectionTableAdapter.GetLoansDetailedInterestProjectionCountRedBal(fromReportDate, toReportDate);
        }
        else
            return -1;
    }
}
